﻿using System;
using System.Collections.Generic;
using System.Linq;
using SGQ.Model;
using System.Data.SqlServerCe;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;

namespace SGQ.Controller
{
    class ControllerUser
    {

        public ControllerUser() { 
            conn = new ConnectFactory();
        }

        private ConnectFactory conn;
        public const string strDelete = "Delete Usuario where Codigo = (@Codigo)";
        public const string strInsert = "Insert into Usuario (Nome,Login,Senha,Tipo,Codigo_Departamento) values (@Nome,@Login,@Senha,@Tipo,@Codigo_Departamento)";
        public const string strSelect = "Select * From Usuario order by Codigo";
        public const string strUpdate = "Update Usuario set Nome = (@Nome), Login = (@Login), Senha = (@Senha), Tipo = (@Tipo), Codigo_Departamento = (@Departamento) Where Codigo = (@Codigo)";
        private ConnectFactory objConexao;

        public bool Insert(Usuario user) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strInsert, objConexao.CreateConnection()))
                    {
                        
                        objCommand.Parameters.AddWithValue("@Nome", user.Nome);
                        objCommand.Parameters.AddWithValue("@Login", user.Login.Name);
                        objCommand.Parameters.AddWithValue("@Senha", user.Login.Senha);
                        objCommand.Parameters.AddWithValue("@Tipo", user.Login.Tipo);
                        objCommand.Parameters.AddWithValue("@Codigo_Departamento", user.Departamento.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }
		
		public bool Update(Usuario user) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strUpdate, objConexao.CreateConnection()))
                    {
                        objCommand.Parameters.AddWithValue("@Nome", user.Nome);
                        objCommand.Parameters.AddWithValue("@Login", user.Login.Name);
                        objCommand.Parameters.AddWithValue("@Senha", user.Login.Senha);
                        objCommand.Parameters.AddWithValue("@Tipo", user.Login.Tipo);
                        objCommand.Parameters.AddWithValue("@Departamento", user.Departamento.Codigo);

						objCommand.Parameters.AddWithValue("@Codigo", user.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public bool Delete(Usuario user) 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strDelete, objConexao.CreateConnection()))
                    {
						objCommand.Parameters.AddWithValue("@Codigo", user.Codigo);
                        int lines = objCommand.ExecuteNonQuery();
                        if (lines > 0)
                        {
                            return true;
                        }
                        else {
                            return false;
                        }
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public List<Usuario> List() 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strSelect, objConexao.CreateConnection()))
                    {
                        SqlCeDataReader dataReader = objCommand.ExecuteReader();
                        List<Usuario> Usuarios = new List<Usuario>();
                        Usuario user = new Usuario();
                        while (dataReader.Read())
                        {
                            user.Codigo = int.Parse(dataReader.GetValue(0).ToString());
                            //user.Departamento = dataReader.GetValue(1).ToString();
                            Usuarios.Add(user);
                        }
                        return (Usuarios);
                        
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }

		public DataSet ListDataSet() 
        {
            try
            {
                using (objConexao = new ConnectFactory())
                {
                    using (SqlCeCommand objCommand = new SqlCeCommand(strSelect, objConexao.CreateConnection()))
                    {
                        SqlCeDataAdapter adapter = new SqlCeDataAdapter(objCommand);
						DataSet dataSet = new DataSet();
						adapter.Fill(dataSet);
						return (dataSet);   
                    }
                }
            }catch (Exception e)
            {
                throw new Exception(e.Message,e);
            }
        }
    }
}

